package com.javaweb.common;

import com.javaweb.entity.Comment;
import com.javaweb.entity.Teacher;
import com.javaweb.entity.User;
import com.javaweb.entity.VoteDetail;
import com.javaweb.vo.CommentVO;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public  class DBUtil {

    public static final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver" ;
    public static final String MYSQL_URL = "jdbc:mysql://localhost:3306/db_vote?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8" ;
    public static final String MYSQL_USER = "root" ;
    public static final String MYSQL_PWD = "mysql8202230017" ;

        private static Connection conn = null ;
        private static ResultSet rs = null ;
        private static PreparedStatement ps = null ;


        public static void getConnection() {
            try {
                if (conn == null || conn.isClosed()) {

                    Class.forName(MYSQL_DRIVER);
                    conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PWD);

                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        public static void doQuery(String sql, Object ... params) {
            try {
                getConnection();
                ps = conn.prepareStatement(sql) ;
                injectParam(ps, params);
                rs = ps.executeQuery() ;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        public static void close() {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        private static void injectParam(PreparedStatement ps, Object... params) throws Exception {

            if (params!= null && params.length > 0 ) {
                for (int i=1; i<=params.length; i++) {
                    ps.setObject(i, params[i-1]);
                }
            }
        }

        public static int doUpate(String sql, Object... params ) {
            try {
                getConnection();
                ps = conn.prepareStatement(sql) ;
                injectParam(ps, params);
                return ps.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close();
            }
            return 0 ;
        }

    /**
     * Following methods are on service level.
     *
     */
    @Deprecated
    public static void updateVote(String teacherName) {
            String sql = "UPDATE t_teacher SET vote=vote+1 WHERE name=?" ;
            if (doUpate(sql, teacherName) > 0) {
                System.out.println("[INFO] update teacher " + teacherName + " successfully.") ;
            } else {
                System.err.println("[ERROR] update teacher " + teacherName + " FAILED.");
            }
        }
        public static List<Teacher> queryAllTeacher() {
            List<Teacher> teacherList = null ;
            Teacher teacher = null ;
            String sql = "SELECT * from t_teacher" ;
            doQuery(sql, null) ;
            try {
                while (rs.next()) {
                    if (teacherList == null) {
                        teacherList = new ArrayList<>() ;
                    }
                    teacher = new Teacher() ;
                    teacher.setId(rs.getInt(1)) ;
                    teacher.setName(rs.getString(2));
                    teacher.setGender(rs.getString(3));
                    teacher.setDesc(rs.getString(4));
                    teacher.setVote(rs.getInt(5));
                    teacher.setImgURL(rs.getString(6));
                    teacher.setFavNum(rs.getInt(7));
                    teacher.setUnfavNum(rs.getInt(8));
                    teacherList.add(teacher) ;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            close() ;
            return teacherList ;
        }

    /**
     * get comments for given teacher.
     * @param teacherId
     * @return
     */
        public static List<Comment> getCommentsForTeacher(int teacherId) {
            List<Comment> commentList = null ;
            Comment comment = null ;
            String sql = "SELECT * FROM t_comment WHERE t_id = ? ORDER BY com_time DESC" ;
            doQuery(sql, teacherId) ;
            try {
                while (rs.next()) {
                    if (commentList == null) {
                        commentList = new ArrayList<>() ;
                    }
                    comment = new Comment() ;
                    comment.setId(rs.getInt(1)) ;
                    comment.setTeacherId(rs.getInt(2));
                    comment.setUserId(rs.getInt(3));
                    comment.setComment(rs.getString(4));
                    comment.setCommentTime(rs.getTimestamp(5));
                    commentList.add(comment) ;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            close() ;
            return commentList ;

        }




    /**
     * check the login user was a valided user.
     * @param name
     * @param pwd
     * @return
     */
        public static User userLogin(String name, String pwd) {
//            String sql = "SELECT * FROM t_user WHERE name=? and pwd=? LIMIT 1" ;
            String sql = "SELECT * FROM t_user WHERE name='" + name
                                        + "' and pwd = '" + pwd + "' LIMIT 1" ;


            User user = null ;
           // doQuery(sql, name, pwd);
            try {
                getConnection();
                Statement st = conn.createStatement() ;
                rs = st.executeQuery(sql) ;
                if (rs.next()) {
                    user = new User() ;
                    user.setId(rs.getInt(1)) ;
                    user.setName(rs.getString(2));
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            close() ;
            return user;
        }

    public static boolean existedUser(String name, String pwd) {
        String sql = "SELECT * FROM t_user WHERE name=? and pwd=? LIMIT 1" ;
        doQuery(sql, name, pwd);
        try {
            if (rs.next()) {
               close() ;
               return true ;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        close() ;
        return false;
    }

    /**
     * do vote for {@code teacherId} by user {@code userId} .
     * @param teacherId
     * @param userId
     * @return
     */
        public static ServiceResponse doVote(int teacherId, int userId, Date currentTime) {
            String sqlOfUpdateTeacher = "UPDATE t_teacher SET vote=vote+1 WHERE id=?" ;
            String sqlOfUpdateVote = "INSERT INTO t_vote (t_id, u_id, vote_time) VALUES (?,?,?)" ;
            // first check whether the use cound make vote, i.e,. the time was beyond 24 hour after last operaton.
            if (canVote(userId, currentTime)) {
                /**
                 * NOTE THAT, the transaction issue was not considered here.
                 */
                doUpate(sqlOfUpdateTeacher, teacherId) ;
                doUpate(sqlOfUpdateVote, teacherId, userId, new Timestamp(currentTime.getTime())) ;
                System.out.println("[INFO] update teacher " + teacherId + " for by user " + userId + " SUCCESSFULLY.") ;
                return ServiceResponse.successResp("vote successfully.") ;
            } else {
                System.err.println("[ERROR] update teacher " + teacherId + ", since the 24-HOUR Restriction was NOT satisfied.");
                return ServiceResponse.errorResp("违反投票24小时原则。") ;
            }

        }
        public static int updateFavNum(int teacherId, int newFavNum) {
            String sql = "UPDATE t_teacher SET fav_num = ? WHERE t_id=?" ;
            doUpate(sql, newFavNum, teacherId) ;
            return 0 ;
        }
        public static int updateUnfavNum(int teacherId, int newUnfavNum) {
            String sql = "UPDATE t_teacher SET unfav_num = ? WHERE t_id=?" ;
            doUpate(sql, newUnfavNum, teacherId) ;
            return 0 ;
        }

        public static int addComment(Comment comment) {
            String sql = "INSERT INTO t_comment (t_id, u_id, comment, com_time) VALUES (?,?,?,?)" ;
            doUpate(sql, comment.getTeacherId(), comment.getUserId(), comment.getComment(), comment.getCommentTime()) ;
            return 0 ;
        }
        public static int addVote(int teacherId, int userId, Date currentTime) {
            String sqlOfUpdateTeacher = "UPDATE t_teacher SET vote=vote+1 WHERE id=?" ;
            String sqlOfUpdateVote = "INSERT INTO t_vote (t_id, u_id, vote_time) VALUES (?,?,?)" ;
            doUpate(sqlOfUpdateTeacher, teacherId) ;
            doUpate(sqlOfUpdateVote, teacherId, userId, new Timestamp(currentTime.getTime())) ;
            return 0 ;
        }


    /**
     * check if the user current vote time satifies 24-hour restriction.
     * @param userId
     * @param currentTime
     * @return
     */
        public static boolean canVote(int userId, Date currentTime) {
            Date lastTime = getLatestVoteTime(userId) ;
            if (lastTime == null) return true ;
            return currentTime.getTime() - lastTime.getTime()
                    > Constant.MILLS_SECONDS_FOR_24_HOURS ;
        }
        public static Date getLatestVoteTime(int userId) {
            String sql = "SELECT vote_time FROM t_vote WHERE u_id = ? ORDER BY vote_time DESC LIMIT 1" ;
            Date lastTime = null ;
            doQuery(sql, userId);
            try {
                if (rs.next()) {
                    lastTime = rs.getTimestamp(1) ;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            close() ;
            return lastTime ;
        }

        public static String getUserNameById(int uId) {
            String sql = "SELECT name FROM t_user WHERE id = ? " ;
            String userName = null ;
            doQuery(sql, uId);
            try {
                if (rs.next()) {
                    userName = rs.getString(1) ;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            close() ;
            return userName ;
        }

    /**
     * save the user to DB
     * @param name
     * @param pwd
     * @param imgUrl
     * @return
     */

    public static int addNewUser(String name, String pwd, String imgUrl) {
        String sql = "INSERT INTO t_user (name, pwd, img) VALUES (?,?,?)" ;
        return doUpate(sql, name, pwd, imgUrl) ;

    }

    /**
     * check the name was a new name
     * @param name
     * @return
     */
    public static boolean existedName(String name) {
        String sql = "SELECT * FROM t_user WHERE name=? LIMIT 1" ;
        doQuery(sql, name);
        try {
            if (rs.next()) {
                close() ;
                return true ;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        close() ;
        return false;
    }




}

